package com.redpack.redpost.util.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//import java.sql.ResultSet;

import com.redpack.redpost.connection.RedpostConexion;
import com.redpack.redpost.util.to.UserTo;
import com.redpack.redpost.util.to.comentariosTo;
import com.redpack.redpost.util.to.generalUtil;

public class ComentarioAdminDao {

	

	public static List getComentarios( int id ) throws SQLException{
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		List comentarios = new ArrayList(); 
		String sql = "" ;
		
		try{			
			
			sql = "select * from redpost.tbl_comentarios where status=1 and cve_usuarioredpost="+id+" order by cve_comentario desc;" ;
			ps = conn.prepareStatement( sql ) ;
			rs = ps.executeQuery();
			
			while (rs.next()) {
				comentariosTo row = new comentariosTo();
				row.setCve_comentario( rs.getInt(1) );
				row.setCve_usuario( rs.getInt(2) );
				row.setComentario( rs.getString(3) );
				row.setFecha( rs.getString(4) );
				row.setTipo( rs.getInt(5) );
				comentarios.add( row );
			}
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			
			return comentarios ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;
			return comentarios ;
		} finally {
            if (conn != null) {
                conn.close();
            }
			
		}
	}
	
	public static List getComentario() throws SQLException{
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null, psCom = null ;
		ResultSet rs = null, rsCom = null ;
		List comentarios = new ArrayList(); 
		String sql = "", sql_com = "";
		
		try{			
			
			//sql = "select c.*,u.nip,u.nombre,u.apellidos from redpost.tbl_usuarioredpost as u left outer join redpost.tbl_comentarios as c on c.cve_usuarioredpost=u.cve_usuarioredpost where c.status=1 order by c.cve_comentario desc;" ;
			sql = "select cve_usuarioredpost,nip,nombre,apellidos from redpost.tbl_usuarioredpost where cve_usuarioredpost in (select cve_usuarioredpost from redpost.tbl_comentarios group by cve_usuarioredpost)";
			
			ps = conn.prepareStatement( sql ) ;
			rs = ps.executeQuery();
			
			while (rs.next()) {
				
				sql_com = "select * from redpost.tbl_comentarios where cve_usuarioredpost="+rs.getInt("cve_usuarioredpost")+" order by fecha desc desc limit 1";
				comentariosTo row = new comentariosTo();
				
				psCom = conn.prepareStatement( sql_com ) ;
				rsCom = psCom.executeQuery();
				
				while (rsCom.next()) {
					row.setCve_comentario( rsCom.getInt("cve_comentario") );
					row.setCve_usuario( rsCom.getInt("cve_usuarioredpost") );
					row.setNip( rs.getString("nip") );
					row.setComentario( rsCom.getString("comentario") );
					row.setNombre( rs.getString("nombre") );
					row.setApellidos( rs.getString("apellidos") );
					row.setFecha( rsCom.getString("fecha") );
					row.setTipo( rsCom.getInt("tipo") );
				}
				
				comentarios.add( row );
				
			}
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			
			return comentarios ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;
			return comentarios ;
		} finally {
            if (conn != null) {
                conn.close();
            }
			
		}
	}
	
	
public static boolean saveComentario( String comentario, int cve_usuario, int tipo  ) throws Exception {
		
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null ;
		boolean res = false ;
		String sql = "" ;
		String fecha = generalUtil.obtenerFechaActual();
		
		try {
			
			sql = "insert into redpost.tbl_comentarios(cve_usuarioredpost,comentario,fecha,tipo,status) values("+cve_usuario+",'"+comentario+"','"+fecha+"',"+tipo+",1);" ;
			ps = conn.prepareStatement( sql ) ;
			ps.execute( ) ;
			
			res = true ;
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			
			return res ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;
			conn.rollback( ) ;
			return res ;
		} finally {
            if (conn != null) {
                conn.close();
            }
			
		}
		
		
	}

}
